from fastapi import APIRouter, Query
from fastapi.params import Depends
from pydantic import Field, BaseModel
from sqlalchemy import text
from sqlalchemy.orm import Session

from commons.BaseResponse import BaseResponse
from commons.PaginationBase import PaginationBase
from datebase import get_db

air_router = APIRouter(prefix="/air", tags=["空气管理"])


class AirSearch(PaginationBase):
    fsiteNo: str = Field(None, description="站点编号")
    sort: str = Field("desc", description="排序方式")


@air_router.get("/getAirPointByFsiteNo", summary="根据场地编号获取设备")
def get_air_point_by_fsiteNo(air: AirSearch = Query(), db: Session = Depends(get_db)):
    sql = (
        " select cast(FAQI as int) faqi, fcontaminants , fun_GetYX(faqi, FCONTAMINANTS)  fYX"
        ", fun_GetCS(faqi, FCONTAMINANTS) fCS , frefresh as fupddate , fsite_d , fsite_l"
        ", round(FWD, 1) fwd , cast(FSD as int) fsd , faqi_tb , faqi_hb"
        " from A_TS_SITE"
        " WHERE  FSITE_NO = :fsiteNo "
    )
    sqlResult = db.execute(text(sql), {
        "fsiteNo": air.fsiteNo,
    }).fetchall()
    if sqlResult is not None and len(sqlResult) > 0:
        for key in range(len(sqlResult)):
            faqi = sqlResult[key][0]
            fcontaminants = sqlResult[key][1]
            fYX = sqlResult[key][2]
            fCS = sqlResult[key][3]
            fupddate = sqlResult[key][4]
            fsiteD = sqlResult[key][5]
            fsiteL = sqlResult[key][6]
            fwd = sqlResult[key][7]
            fsd = sqlResult[key][8]
            faqiTb = sqlResult[key][9]
            faqiHb = sqlResult[key][10]
            data = {"faqi": faqi, "fcontaminants": fcontaminants, "fYX": fYX,
                    "fCS": fCS, "fupddate": fupddate, "fsiteD": fsiteD, "fsiteL": fsiteL,
                    "fwd": fwd, "fsd": fsd, "faqiTb": faqiTb, "faqiHb": faqiHb}
            return BaseResponse.success(data=data)
    return BaseResponse.error(message="未查询到设备信息")


@air_router.get("/getNoiseByFsiteNo", summary="大屏数据 室外噪音")
def get_noise_by_fsiteno(air: AirSearch = Query(), db: Session = Depends(get_db)):
    sql = "select fwd,fsd,noise from a_ts_site where fsite_no =  :fsiteno   limit 1;"
    sqlResult = db.execute(text(sql), {
        "fsiteno": air.fsiteNo,
    }).fetchall()
    if (sqlResult is not None and len(sqlResult) > 0):
        for key in range(len(sqlResult)):
            fwd = sqlResult[key][0]
            fsd = sqlResult[key][1]
            noise = sqlResult[key][2]
            data = {"fsd": fsd, "fwd": fwd, "noise": noise}
            return BaseResponse.success(data=data)
    return BaseResponse.error(message="未查询到该站点噪音信息")


@air_router.get("/getAQIByFsiteNo", summary="大屏数据，获取24小时api")
def get_aqi_by_fsiteno(air: AirSearch = Query(), db: Session = Depends(get_db)):
    sql = (
        "select FTIME datePart,a.fmoniter_so2,a.fmoniter_no2,a.fmoniter_co,a.fmoniter_o3,a.fmoniter_pm25,a.fmoniter_pm10,a.faqi"
        " from A_TH_SITE_MONITER a,A_TS_SITE c"
        " where c.FSITE_NO= :fsiteNo "
        " and c.FSITE_NO=a.FSITE_NO"
    )
    if air.order == "desc":
        sql = sql + " order by datePart desc "
    else:
        sql = sql + " order by datePart "
    sqlResult = db.execute(text(sql), {
        "fsiteNo": air.fsiteNo,
    }).fetchall()
    resultData = []
    if sqlResult is not None and len(sqlResult) > 0:
        for key in range(len(sqlResult)):
            datePart = sqlResult[key][0]
            fmoniterSo2 = sqlResult[key][1]
            fmoniterNo2 = sqlResult[key][2]
            fmoniterCo = sqlResult[key][3]
            fmoniter03 = sqlResult[key][4]
            fmoniterPm25 = sqlResult[key][5]
            fmoniterPm10 = sqlResult[key][6]
            faqi = sqlResult[key][7]
            data = {"datePart": datePart, "fmoniterSo2": fmoniterSo2, "fmoniterNo2": fmoniterNo2,
                    "fmoniterCo": fmoniterCo, "fmoniter03": fmoniter03,
                    "fmoniterPm25": fmoniterPm25, "fmoniterPm10": fmoniterPm10, "faqi": faqi}
            resultData.append(data)
    commonResult = {"list": resultData}
    return BaseResponse.success(data=commonResult)


@air_router.get("/getStateByFsiteNo", summary="大屏数据 获取空气设备的联网状态")
def get_state_by_fsiteno(air: AirSearch = Query(), db: Session = Depends(get_db)):
    sql = "select fnewwork,f from PROC_RPT_NETWORK( :fsiteNo )"
    sqlResult = db.execute(text(sql), {"fsiteNo": air.fsiteNo}).fetchall()
    resultData = []
    if sqlResult is not None and len(sqlResult) > 0:
        for key in range(len(sqlResult)):
            fnewwork = sqlResult[key][0]
            f = sqlResult[key][1]
            data = {"fnewwork": fnewwork, "f": f, "eqtInvVolis": None, "fsiteName": None}
            resultData.append(data)

    commonResult = {"list": resultData}
    return BaseResponse.success(data=commonResult)


class StationSearch(BaseModel):
    fsiteNo: str = Field(None, description="站点编号")
    fpointFlag: str = Field(None, description="")


@air_router.get("/getStationBYFsiteNo", summary="获取设备（室外和室内）AQI（fpointFlag 1：室外，2：室内总站，3：室外和总站")
def get_station_by_fsiteno(station: StationSearch = Query(), db: Session = Depends(get_db)):
    sql = ("select b.fpoint_name,a.fpoint_no,a.faqi,b.fpoint_h,b.fpoint_w,b.fpoint_l,b.fpoint_d"
           " from a_TH_POINT_MONITER a,a_TS_POINT b,a_TS_SITE c"
           " where c.fsite_no= :fsiteNo and c.FSITE_NO=b.FSITE_NO and b.FPOINT_NO=a.FPOINT_NO")
    if (station.fpointFlag == 3):
        sql = sql + " and (b.FPOINT_FLAG=1 or b.FPOINT_FLAG=2)"
    if (station.fpointFlag != 3):
        sql = sql + " and b.FPOINT_FLAG= :fpointFlag "
    sql = sql + " order by a.fpoint_no"
    sqlResult = db.execute(text(sql), {
        "fsiteNo": station.fsiteNo,
        "fpointFlag": station.fpointFlag,
    }).fetchall()
    resultData = []
    if sqlResult is not None and len(sqlResult) > 0:
        for key in range(len(sqlResult)):
            fpointName = sqlResult[key][0]
            fpointNo = sqlResult[key][1]
            faqi = sqlResult[key][2]
            fpointH = sqlResult[key][3]
            fpointW = sqlResult[key][4]
            fpointL = sqlResult[key][5]
            fpointD = sqlResult[key][6]
            item = {"fmoniterPm25": 0, "fpointName": fpointName, "fpointNo": fpointNo, "faqi": faqi,
                    "fpointH": fpointH, "fpointW": fpointW, "fpointL": fpointL, "fpointD": fpointD}
            resultData.append(item)
    commonResult = {"list": resultData}
    return BaseResponse.success(data=commonResult)


@air_router.get("/getPointDataByFsiteNo", summary="数据大屏 通过区地区编号获取总站空气数据")
def get_point_data_by_fsiteno(station: StationSearch = Query(), db: Session = Depends(get_db)):
    sql = ("select b.fpoint_name,a.fpoint_no,a.fwd,a.fsd,a.fmoniter_pm25,a.fmoniter_pm10,a.fmoniter_co2,a.fmoniter_jq,"
           "a.fmoniter_yjw,b.fpoint_h,b.fpoint_w,b.fpoint_l,b.fpoint_d,to_char(a.fpoint_refresh, 'HH24:MI:SS') ftime"
           " from a_TH_POINT_MONITER a,a_TS_POINT b"
           " where b.FSITE_NO= :fsiteNo "
           " and b.FPOINT_NO=a.FPOINT_NO"
           " and b.FPOINT_FLAG = '2'"
           " order by a.fpoint_no"
           )
    sqlResult = db.execute(text(sql), {
        "fsiteNo": station.fsiteNo,
    }).fetchall()
    resultData = []
    if sqlResult is not None and len(sqlResult) > 0:
        for key in range(len(sqlResult)):
            fpointName = sqlResult[key][0]
            fpointNo = sqlResult[key][1]
            fwd = sqlResult[key][2]
            fsd = sqlResult[key][3]
            fmoniterPm25 = sqlResult[key][4]
            fmoniterPm10 = sqlResult[key][5]
            fmoniterCo2 = sqlResult[key][6]
            fmoniterJq = sqlResult[key][7]
            fmoniterYjw = sqlResult[key][8]
            fpointH = sqlResult[key][9]
            fpointW = sqlResult[key][10]
            fpointL = sqlResult[key][11]
            fpointD = sqlResult[key][12]
            ftime = sqlResult[key][13]
            item = {"fpointName": fpointName, "fpointNo": fpointNo, "fwd": fwd, "fsd": fsd,
                    "fmoniterPm25": fmoniterPm25,
                    "fmoniterPm10": fmoniterPm10, "fmoniterCo2": fmoniterCo2, "fmoniterJq": fmoniterJq,
                    "fmoniterYjw": fmoniterYjw,
                    "fpointH": fpointH, "fpointW": fpointW, "fpointL": fpointL, "fpointD": fpointD, "ftime": ftime,
                    "co": None, "faqi": 0,
                    "fequipmentbarcode": None, "fequipmenttag": None, "fmacn": None, "fnewwork": None, "no2": None,
                    "o3": None, "pm1": None,
                    "so2": None, "time": ftime}
            resultData.append(item)
    commonResult = {"list": resultData}
    return BaseResponse.success(data=commonResult)


@air_router.get("/getAirPointByFsiteNo2", summary="数据大屏 学校室内空气指数")
def get_air_point_by_fsiteno2(station: StationSearch = Query(), db: Session = Depends(get_db)):
    sql = ("select faqi,fcontaminants,round(FWD,1) wd,cast(FSD as int) sd,frefresh as fupddate"
           ",fun_GetYX(faqi,FCONTAMINANTS) fYX"
           ",fun_GetCS(faqi,FCONTAMINANTS) fCS"
           " from a_Ts_site"
           " where 1=1"
           " and fsite_no= ：fsiteNo ")
    sqlResult = db.execute(text(sql), {
        "fsiteNo": station.fsiteNo,
    }).fetchall()
    data = None
    if sqlResult is not None and len(sqlResult) > 0:
        for key in range(len(sqlResult)):
            faqi = sqlResult[key][0]
            fcontaminants = sqlResult[key][1]
            wd = sqlResult[key][2]
            sd = sqlResult[key][3]
            fupddate = sqlResult[key][4]
            fyx = sqlResult[key][5]
            fcs = sqlResult[key][6]
            data = {"faqi": faqi, "fcontaminants": fcontaminants, "wd": wd, "sd": sd, "fupddate": fupddate, "fyx": fyx,
                    "fcs": fcs, "fmoniterPm10": None, "fmoniterPm25": None, "fpointNo": None}
    return BaseResponse.success(data=data)


@air_router.get("/getAqiDataByFsiteNo", summary="数据大屏 室内24小时空气指标")
def get_aqi_data_by_fsiteno(station: StationSearch = Query(), db: Session = Depends(get_db)):
    sql = ("select fsite_no,date_part('hour',FMONITER_DATE) fhour,fmoniter_pm26 fpm25,"
           "fmoniter_pm11 fpm10,fmoniter_co2 fco2,fmoniter_jq fjq,fmoniter_yjw fyjw,fsd2,fwd2"
           " from A_TC_SITE_MONITER"
           " where 1=1"
           " and fsite_no= :fsiteNo "
           " and to_char(fmoniter_date,'yyyy-mm-dd')=to_char(current_date,'yyyy-mm-dd')"
           " order by 2")
    sqlResult = db.execute(text(sql), {
        "fsiteNo": station.fsiteNo,
    }).fetchall()
    resultData = []
    if sqlResult is not None and len(sqlResult) > 0:
        for key in range(len(sqlResult)):
            fsiteNo = sqlResult[key][0]
            fhour = sqlResult[key][1]
            fpm25 = sqlResult[key][2]
            fpm10 = sqlResult[key][3]
            fco2 = sqlResult[key][4]
            fjq = sqlResult[key][5]
            fyjw = sqlResult[key][6]
            fsd2 = sqlResult[key][7]
            fwd2 = sqlResult[key][8]
            item = {"fsiteNo": fsiteNo, "fhour": fhour, "fpm25": fpm25, "fpm10": fpm10, "fco2": fco2,
                    "fjq": fjq, "fyjw": fyjw, "fsd2": fsd2, "fwd2": fwd2, "fpm26": None, "fpointName": None,
                    "fpointNo": None, "fsiteName": None}
            resultData.append(item)

    commonResult = {"list": resultData}
    return BaseResponse.success(data=commonResult)


class AirDataSearch(BaseModel):
    fpointNo: str = Field(None, description="站点编号")


@air_router.get("/getAirDataByFpointNo", summary="数据大屏 某个总站内各点空气数据")
def get_air_data_by_fpointno(air: AirDataSearch = Query(), db: Session = Depends(get_db)):
    sql = (
        "select b.fpoint_name,"
        "a.fpoint_no,"
        "COALESCE(a.fwd, 0) as fwd,"
        "COALESCE(a.fsd, 0) as fsd,"
        "COALESCE(a.fmoniter_pm25, 0) as fmoniter_pm25,"
        "COALESCE(a.fmoniter_pm10, 0) as fmoniter_pm10,"
        "COALESCE(a.fmoniter_co2, 0) as fmoniter_co2,"
        "COALESCE(a.fmoniter_jq, 0) as fmoniter_jq,"
        "COALESCE(a.fmoniter_yjw, 0) as fmoniter_yjw,"
        "b.fpoint_h,"
        "b.fpoint_w,"
        "b.fpoint_l,"
        "b.fpoint_d,"
        "to_char(a.fpoint_refresh, 'HH24:MI:SS') ftime,"
        "a.fnewwork,"
        "COALESCE(a.fmoniter_co, 0) as co,"
        "COALESCE(a.fmoniter_pm1, 0) as pm1,"
        "COALESCE(a.fmoniter_yjw, 0) as fmoniter_yjw"
        " from a_TH_POINT_MONITER a, a_TS_POINT b"
        " where b.FPARENT_NO = :fpointNo "
        " and b.FPOINT_NO = a.FPOINT_NO"
        " order by a.fpoint_no"
    )
    sqlResult = db.execute(text(sql), {
        "fpointNo": air.fpointNo,
    }).fetchall()
    resultData = []
    if sqlResult is not None and len(sqlResult) > 0:
        for key in range(len(sqlResult)):
            fpointName = sqlResult[key][0]
            fpointNo = sqlResult[key][1]
            fwd = sqlResult[key][2]
            fsd = sqlResult[key][3]
            fmoniterPm25 = sqlResult[key][4]
            fmoniterPm10 = sqlResult[key][5]
            fmoniterCo2 = sqlResult[key][6]
            fmoniterJq = sqlResult[key][7]
            fmoniterYjw = sqlResult[key][8]
            fpointH = sqlResult[key][9]
            fpointW = sqlResult[key][10]
            fpointL = sqlResult[key][11]
            fpointD = sqlResult[key][12]
            ftime = sqlResult[key][13]
            fnewwork = sqlResult[key][14]
            co = sqlResult[key][15]
            pm1 = sqlResult[key][16]
            fmoniterYjw = sqlResult[key][17]
            item = {"fpointName": fpointName, "fpointNo": fpointNo, "fwd": fwd, "fsd": fsd,
                    "fmoniterPm25": fmoniterPm25, "fmoniterPm10": fmoniterPm10, "fmoniterCo2": fmoniterCo2,
                    "fmoniterJq": fmoniterJq, "fmoniterYjw": fmoniterYjw, "fpointH": fpointH, "fpointW": fpointW,
                    "fpointL": fpointL, "fpointD": fpointD, "time": ftime, "fnewwork": fnewwork,
                    "co": co,
                    "pm1": pm1,
                    "fequipmentbarcode": None, "fequipmenttage": None, "fmacn": None,
                    "no2": None, "o3": None, "so2": None, "faqi": 0}
            resultData.append(item)

    commonResult = {"list": resultData}
    return BaseResponse.success(data=commonResult)


@air_router.get("/getAirPointByFpointNo", description="数据大屏 根据fpointNo获取空气指数")
def get_air_point_by_fpoint_no(air: AirSearch = Query(), db: Session = Depends(get_db)):
    sql = (
        " select fpoint_no, faqi, fmoniter_pm25, fmoniter_pm10, fwd wd, fsd sd, fun_GetYX(faqi, FCONTAMINANTS) fYX , fun_GetCS(faqi, FCONTAMINANTS) fCS, fcontaminants, fpoint_refresh fupddate"
        " from A_TH_POINT_MONITER"
        " where  1 = 1"
        " and fpoint_no = :fsiteNo "
    )
    sqlResult = db.execute(text(sql), {
        "fsiteNo": air.fsiteNo,
    }).fetchall()
    resultData = {}
    if sqlResult is not None and len(sqlResult) > 0:
        for key in range(len(sqlResult)):
            fpointNo = sqlResult[key][0]
            faqi = sqlResult[key][1]
            fmoniterPm25 = sqlResult[key][2]
            fmoniterPm10 = sqlResult[key][3]
            wd = sqlResult[key][4]
            sd = sqlResult[key][5]
            fyx = sqlResult[key][6]
            fcs = sqlResult[key][7]
            fcontaminants = sqlResult[key][8]
            fupddate = sqlResult[key][9]
            resultData = {"fpointNo": fpointNo, "faqi": faqi, "fmoniterPm25": fmoniterPm25,
                          "fmoniterPm10": fmoniterPm10,
                          "wd": wd, "sd": sd, "fyx": fyx, "fcs": fcs, "fcontaminants": fcontaminants,
                          "fupddate": fupddate}
    return BaseResponse.success(data=resultData)


@air_router.get("/getAirDataOutdoorByFpointNo", summary="数据大屏  某个室外各点空气数据")
def get_air_data_outdoor_by_fpointno(air: AirDataSearch = Query(), db: Session = Depends(get_db)):
    sql = (
        "select date_part('hour', FMONITER_DATE) datePart, faqi, fmoniter_date , fmoniter_pm25, fmoniter_pm10, fmoniter_no2, fmoniter_so2 , fmoniter_co, fmoniter_o3, fmoniter_jq, fwd2, fsd2"
        " from A_TN_POINT_MONITER"
        " where FPOINT_NO = :fpointNo "
        " and to_char(FMONITER_DATE, 'YYYY-MM-dd hh24:mi:ss') between to_char(NOW() - interval '1 day', 'YYYY-MM-DD') || ' ' || date_part('hour', NOW() - interval '24 hour') || ':00:01'"
        " and to_char(NOW(), 'YYYY-MM-DD') || ' ' || date_part('hour', NOW() - interval '0 hour') || ':00:01'"
        " order  by fmoniter_date;"
    )
    sqlResult = db.execute(text(sql), {
        "fpointNo": air.fpointNo,
    }).fetchall()
    resultData = []
    if (len(sqlResult)):
        for key in range(len(sqlResult)):
            datePart = sqlResult[key][0]
            faqi = sqlResult[key][1]
            fmoniterDate = sqlResult[key][2]
            fmoniterPm25 = sqlResult[key][3]
            fmoniterPm10 = sqlResult[key][4]
            fmoniterNo2 = sqlResult[key][5]
            fmoniterSo2 = sqlResult[key][6]
            fmoniterCo = sqlResult[key][7]
            fmoniterO3 = sqlResult[key][8]
            fmoniterJq = sqlResult[key][9]
            fwd2 = sqlResult[key][10]
            fsd2 = sqlResult[key][11]
            item = {"datePart": datePart, "faqi": faqi, "fmoniterDate": fmoniterDate, "fmoniterPm25": fmoniterPm25,
                    "fmoniterPm10": fmoniterPm10,
                    "fmoniterNo2": fmoniterNo2, "fmoniterSo2": fmoniterSo2, "fmoniterCo": fmoniterCo,
                    "fmoniterO3": fmoniterO3, "fmoniterJq": fmoniterJq,
                    "fwd2": fwd2, "fsd2": fsd2}
            resultData.append(item)
    commonResult = {"list": resultData}
    return BaseResponse.success(data=commonResult)


class AirBoxSearch(BaseModel):
    fsiteNo: str = Field(None, description="站点编号")
    status: str = Field(None, description="状态")


@air_router.get("/getAirBoxListByStatus", summary="数据大屏  获取对应状态的空气盒子")
def get_air_box_list_by_status(airbox: AirBoxSearch = Query(), db: Session = Depends(get_db)):
    sql = " select * from PROC_RPT_NETWORKDATA( :fsiteNo , :status );"
    sqlResult = db.execute(text(sql), {
        "fsiteNo": airbox.fsiteNo,
        "status": airbox.status,
    }).fetchall()
    resultData = []
    if (len(sqlResult)):
        for key in range(len(sqlResult)):
            fsiteName = sqlResult[key][0]
            province = sqlResult[key][1]
            fzone = sqlResult[key][2]
            fpointName = sqlResult[key][3]
            fpointRefresh = sqlResult[key][4]
            item = {"fsiteName": fsiteName, "province": province, "fzone": fzone, "fpointName": fpointName,
                    "fpointRefresh": fpointRefresh}
            resultData.append(item)
    commonResult = {"list": resultData}
    return BaseResponse.success(data=commonResult)
